-- 1.查看所有文件组(包括主文件组)
select * from sys.filegroups;
-- 2.查看文件及文件组的语句
select df.name, df.physical_name, df.size, df.growth,
	   f.name filegroup, f.is_default
from sys.database_files df
     join sys.filegroups f on df.data_space_id = f.data_space_id;


-- 3.查看分区方案
select * from sys.partition_schemes;
-- 4.删除分区方案
drop partition scheme pro_csi_scheme;

--5.查看分区函数
select * from sys.partition_functions;	  
--6.删除分区函数
drop partition function pro_csi_part_fun;


-- 查看数据所在分区序号
select csi.check_scheme_id, 分区序号 = $partition.pro_csi_part_fun(check_scheme_id) 
from cost.pro_cost_cal_serve_indirect csi
group by csi.check_scheme_id;

-- 查看分区在哪个文件组
select fg.name, 分区号=sp.destination_id
from sys.destination_data_spaces sp
     inner join sys.partition_schemes scm on sp.partition_scheme_id = scm.data_space_id
     inner join sys.filegroups fg on fg.data_space_id = sp.data_space_id
where scm.name='pro_csi_scheme';

-- 查看分区表、分区方案、分区函数等信息
select 
    t.name as table_name,
    s.name as schema_name,
    pf.name as partition_function_name,
    ps.name as partition_scheme_name,
    i.name
from sys.tables t
     join sys.schemas s on t.schema_id = s.schema_id
     join sys.indexes i on t.object_id = i.object_id
     join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
     join sys.partition_functions pf on ps.function_id = pf.function_id;
	 
-- 查看分区函数值
select 
    pf.name as partition_function_name,
    pf.function_id,
    prgv.boundary_id,
    prgv.value as boundary_value
from sys.partition_functions pf
     join sys.partition_range_values prgv on pf.function_id = prgv.function_id
where pf.name = 'pro_csi_part_fun';


-- 7.创建分区函数
declare @num int = 1;
declare @part_id_str nvarchar(max) = '';
declare @part_fun_sql nvarchar(max) = 'create partition function pro_csi_part_fun(int) as range left for values(';
begin

	while @num <= 130
    begin
	   set @part_id_str = concat(@part_id_str, cast(@num as varchar(3)), ',');
       set @num = @num + 1;
    end;

	set @part_id_str = left(@part_id_str, len(@part_id_str) - 1);
	set @part_fun_sql = concat(@part_fun_sql, @part_id_str, ');');

	exec sp_executesql @part_fun_sql;
end;

-- 8.创建分区方案
declare @num int = 1;
declare @scheme_id_str nvarchar(max) = '';
declare @scheme_sql nvarchar(max) = 'create partition scheme pro_csi_scheme as partition pro_csi_part_fun to(';
begin

	while @num <= 131
    begin
	   set @scheme_id_str = concat(concat(@scheme_id_str, 'pcsi_'), cast(@num as varchar(3)), ',');
       set @num = @num + 1;
    end;

	set @scheme_id_str = left(@scheme_id_str, len(@scheme_id_str) - 1);
	set @scheme_sql = concat(@scheme_sql, @scheme_id_str, ');');

	exec sp_executesql @scheme_sql;
end;

-- 创建分区表
create table cost.pro_cost_cal_serve_indirect_new(
  id int identity(1,1) not null,
  cal_res_id int  null,
  check_scheme_id int null,
  organization_id int null,
  hospital_structure_id int null,
  organization_cal_method int null,
  serve_item_code varchar(50) null,
  cost_item_code varchar(20) null,
  number decimal(18,4) null,
  price decimal(18,4) null,
  indirect_cost_bk decimal(30,18) null,
  indirect_cost_gl decimal(30,18) null,
  indirect_cost_yf decimal(30,18) null,
  medical_cost_type int null,
  create_user_code varchar(20) null,
  create_time datetime null,
  update_user_code varchar(20) null,
  update_time datetime null,
  delete_flag tinyint null,
  calculate_method int null,
  medical_signage int default null
) on pro_csi_scheme(check_scheme_id);

create nonclustered index idx_pro_cost_cal_serve_indirect
on cost.pro_cost_cal_serve_indirect_new(id);

create nonclustered index idx_non_clu_pro_cost_cal_serve_indirect
on cost.pro_cost_cal_serve_indirect_new(check_scheme_id, organization_id);

create nonclustered index idx_non_clu_pro_cost_cal_serve_indirect2
on cost.pro_cost_cal_serve_indirect_new(cal_res_id);

EXEC sp_addextendedproperty
'MS_Description', N'项目成本核算主表id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'cal_res_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'核算方案主表id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'check_scheme_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'科室id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'organization_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'院区id',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'hospital_structure_id'
;

EXEC sp_addextendedproperty
'MS_Description', N'核算方法',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'organization_cal_method'
;

EXEC sp_addextendedproperty
'MS_Description', N'服务项目编码',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'serve_item_code'
;

EXEC sp_addextendedproperty
'MS_Description', N'成本项目编码',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'cost_item_code'
;

EXEC sp_addextendedproperty
'MS_Description', N'数量',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'number'
;

EXEC sp_addextendedproperty
'MS_Description', N'单价',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'price'
;

EXEC sp_addextendedproperty
'MS_Description', N'本科-间接',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'indirect_cost_bk'
;

EXEC sp_addextendedproperty
'MS_Description', N'管理分摊',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'indirect_cost_gl'
;

EXEC sp_addextendedproperty
'MS_Description', N'医辅分摊',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'indirect_cost_yf'
;

EXEC sp_addextendedproperty
'MS_Description', N'成本维度',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'medical_cost_type'
;

EXEC sp_addextendedproperty
'MS_Description', N'创建人',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'create_user_code'
;

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'create_time'
;

EXEC sp_addextendedproperty
'MS_Description', N'修改人',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'update_user_code'
;

EXEC sp_addextendedproperty
'MS_Description', N'修改时间',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'update_time'
;

EXEC sp_addextendedproperty
'MS_Description', N'删除标记:0.存在,1.已删除',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'delete_flag'
;

EXEC sp_addextendedproperty
'MS_Description', N'计算方法:1303.当量法,1801.时间驱动作业成本法',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'calculate_method'
;

EXEC sp_addextendedproperty
'MS_Description', N'医护标识:4301.医生项目,4302.护理项目',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new',
'COLUMN', N'medical_signage'
;

EXEC sp_addextendedproperty
'MS_Description', N'项目间接成本值表',
'SCHEMA', N'cost',
'TABLE', N'pro_cost_cal_serve_indirect_new'
;

-- 将原始表的数据导入新的分区表中
SET IDENTITY_INSERT cost.pro_cost_cal_serve_indirect_new ON;

insert into cost.pro_cost_cal_serve_indirect_new(
       id, cal_res_id, check_scheme_id, organization_id,
       hospital_structure_id, organization_cal_method, serve_item_code, cost_item_code,
	   number, price, indirect_cost_bk, indirect_cost_gl,
	   indirect_cost_yf, medical_cost_type, create_time, update_time,
	   delete_flag, calculate_method, medical_signage
       )
select id, cal_res_id, check_scheme_id, organization_id,
       hospital_structure_id, organization_cal_method, serve_item_code, cost_item_code,
	   number, price, indirect_cost_bk, indirect_cost_gl,
	   indirect_cost_yf, medical_cost_type, create_time, update_time,
	   delete_flag, calculate_method, medical_signage
from cost.pro_cost_cal_serve_indirect;

SET IDENTITY_INSERT cost.pro_cost_cal_serve_indirect_new OFF;

-- 验证数据是否成功导入到新的分区表中（执行一些查询）
-- select count(1) from cost.pro_cost_cal_serve_indirect_new;

-- 删除原始表并将新的分区表重命名为原始表的名称
drop table cost.pro_cost_cal_serve_indirect;
exec sp_rename 'cost.pro_cost_cal_serve_indirect_new', 'pro_cost_cal_serve_indirect';


